﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.DALFactory;
using KPIS.DBM;


namespace KPIS.GERP.GHRMS.BLL
{
    public class SalaryGroupBLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        SalaryGroupDAL DAL = new SalaryGroupDAL();

        public DataSet LoadSalaryGroup(Nullable<int> Sequence, Nullable<int> EmployeeType, Nullable<int> PositionType, Nullable<int> ClassSeq)
        {
            return DAL.LoadSalaryGroup(Sequence, EmployeeType, PositionType, ClassSeq);
        }
        public DataSet LoadSalaryDtls(Nullable<int> Sequence, Nullable<int> ClassSeq)
        {
            return DAL.LoadSalaryDtls(Sequence, ClassSeq);
        }
        public DataSet ListSalaryGroup(string active, string EmpTypeSeq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", active);
            dbManager.AddParameters(1, "@EmpTypeSeq", EmpTypeSeq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text
                , "select * from HRM_PR_SALARY_GROUPS AS SG"
                + ", hrm_ps_employee_types AS ET"
                + ", hrm_mp_position_types AS PT"
                + ", hrm_mp_classes AS C"
                + ", mas_record_statuses as RS"
                + " WHERE SG.emp_type_seq = ET.emp_type_seq"
                    + " and SG.pos_type_seq = PT.pos_type_seq"
                    + " and SG.class_seq = C.class_seq"
                    + " and SG.record_status = RS.record_status"
                    + " and SG.record_status = @RecordStatus"
                    + " and SG.emp_type_seq = @EmpTypeSeq");
             
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryGroupGovernment(string active, string EmpTypeSeq)
        {
            int nEmpType = EmpTypeSeq.Length > 0 ? Int32.Parse(EmpTypeSeq) : 0;
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", active);
            dbManager.AddParameters(1, "@EmpTypeSeq", nEmpType);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text
                , "select * from HRM_PR_SALARY_GROUPS AS SG"
                + " left join hrm_ps_employee_types AS ET on SG.emp_type_seq = ET.emp_type_seq"
                + " left join hrm_mp_position_types AS PT on SG.pos_type_seq = PT.pos_type_seq"
                + " left join hrm_mp_classes AS C on SG.class_seq = C.class_seq"
                + " left join hrm_pr_salary_group_dtls AS GD on SG.sal_grp_seq = GD.sal_grp_seq"
                + " left join mas_record_statuses AS RS on SG.record_status = RS.record_status"
                + " WHERE SG.record_status = @RecordStatus"
                    + " and SG.emp_type_seq = @EmpTypeSeq");
            //+ " and (SG.RECORD_STATUS IN ('"+ active +"')) ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }
        public DataTable SelectSalaryGroup(Nullable<int> ClassSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@ClassSeq", ClassSeq);

                string strSQL = "SELECT d.*"
                    + " FROM HRM_PR_SALARY_GROUP_DTLS d"
                        + " INNER JOIN HRM_PR_SALARY_GROUPS g ON d.SAL_GRP_SEQ = g.SAL_GRP_SEQ"
                    + " WHERE d.RECORD_STATUS = @ActiveRecord"
                    + (ClassSeq.HasValue ? " AND g.CLASS_SEQ = @ClassSeq" : "");
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
            }

        }
        public bool ChkDuplicate(int emp_type_seq, int pos_type_seq, int class_seq, string record_status, int? sal_grp_seq = null)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from HRM_PR_SALARY_GROUPS where emp_type_seq = " + emp_type_seq +
                " and pos_type_seq = " + pos_type_seq +
                " and class_seq = " + class_seq +
                (sal_grp_seq.HasValue ? " and class_seq = " + sal_grp_seq.Value : "") +
                " and record_status = '" + record_status + "'");
            if (ds.Tables[0].Rows.Count != 0)
                return true;
            else
                return false;
        } 


        public void Insert(int emp_type_seq, int pos_type_seq, int class_seq, string remarks, string record_status, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(1, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(2, "@class_seq", class_seq);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@RecordStatus", record_status);
                dbManager.AddParameters(5, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "INSERT INTO HRM_PR_SALARY_GROUPS (emp_type_seq, pos_type_seq, class_seq,remarks, RECORD_STATUS,created_by,created_when) VALUES (@emp_type_seq, @pos_type_seq, @class_seq, @remarks, @RecordStatus,@CreatedBy,@CreatedWhen)");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void Update(int Sequence, int emp_type_seq, int pos_type_seq, int class_seq, string remarks, string record_status, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(2, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(3, "@class_seq", class_seq);
                dbManager.AddParameters(4, "@remarks", remarks);
                dbManager.AddParameters(5, "@RecordStatus", record_status);
                dbManager.AddParameters(6, "@updated_by", UpdateBy);
                dbManager.AddParameters(7, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE HRM_PR_SALARY_GROUPS SET emp_type_seq=@emp_type_seq, pos_type_seq=@pos_type_seq,class_seq=@class_seq,remarks=@remarks,record_status=@RecordStatus,updated_by=@updated_by,updated_when=@updated_when WHERE sal_grp_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void Delete(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE HRM_PR_SALARY_GROUPS SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE sal_grp_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

    }
}
